// 加载Express模块
const express = require("express");

// 加载MySQL模块
const mysql = require("mysql");

// 加载bodyParser模块
const bodyParser = require("body-parser");

// 加载MD5模块
const md5 = require("md5");

// 创建MySQL连接池
const pool = mysql.createPool({
  host: "127.0.0.1", //MySQL服务器地址
  port: 3306, //MySQL服务器端口号
  user: "root", //数据库用户的用户名
  password: "root", //数据库用户密码
  database: "cpoem", //数据库名称
  connectionLimit: 20, //最大连接数
  charset: "utf8", //数据库服务器的编码方式
});

// 创建服务器对象
const server = express();

server.use(
  bodyParser.urlencoded({
    extended: false,
  })
);

// 加载CORS模块
const cors = require("cors");

// 使用CORS中间件
server.use(
  cors({
    origin: "*",
  })
);
// 指定服务器对象监听的端口号
server.listen(8000, () => {
  console.log("server is running...");
});

//用户注册接口
server.post("/register", (req, res) => {
  //console.log(md5('12345678'));
  // 获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  //以username为条件进行查找操作，以保证用户名的唯一性
  let sql = "SELECT COUNT(id) AS count FROM users WHERE username=?";
  pool.query(sql, [username], (error, results) => {
    if (error) throw error;
    let count = results[0].count;
    if (count == 0) {
      // 将用户的相关信息插入到数据表
      sql = "INSERT users(username,password) VALUES(?,?)";
      pool.query(sql, [username, password], (error, results) => {
        if (error) throw error;
        res.send({ message: "ok", code: 200 });
      });
    } else {
      res.send({ message: "user exists", code: 201 });
    }
  });
});

// 用户登录接口
server.post("/login", (req, res) => {
  //获取用户名和密码信息
  let username = req.body.username;
  let password = req.body.password;
  // SQL语句
  let sql = "SELECT username,id FROM users WHERE username=? AND password=?";
  pool.query(sql, [username, password], (error, results) => {
    if (error) throw error;
    if (results.length == 0) {
      //登录失败
      res.send({ message: "login failed", code: 201 });
    } else {
      //登录成功
      res.send({ message: "ok", code: 200, result: results[0] });
    }
  });
});

//获取所有诗分类的接口
//隋代，先秦，近现代，明代，三国两晋，宋代，清代，南北朝，元代，汉代
// 页数 1，2，3
server.get("/poe", (req, res) => {
  // console.log(req.query.dynasty);
  let n = req.query.page * 1;
  let sql = `SELECT id,poem_name FROM poems where dynasty='${
    req.query.dynasty
  }' limit ${(n - 1) * 10}, 10;`;
  // 执行SQL语句
  console.log(sql);
  let sql2 = `SELECT COUNT(dynasty) AS count FROM poems WHERE dynasty='${req.query.dynasty}';`;
  pool.query(sql2, (error, results) => {
    if (error) throw error;
    let rowcount = results[0].count;
    let pagecount = Math.ceil(rowcount / 10);
    // console.log(pagecount);
    pool.query(sql, (error, results) => {
      if (error) throw error;
      // console.log(results);
      res.send({
        message: "ok",
        code: 200,
        results: results,
        pagecount: pagecount,
      });
    });
  });
});

//搜索框搜索接口  参数：poe page
server.get("/select", (req, res) => {
  // console.log(req.query.dynasty);
  let n = req.query.page * 1;

  let sql = `select * from poems where  author_name like '%${
    req.query.poe
  }%' || poem_name like '%${req.query.poe}%' || poem like '%${
    req.query.poe
  }%' limit ${(n - 1) * 10}, 10;`;
  // 执行SQL语句`
  // 执行SQL语句
  // console.log(sql);
  pool.query(sql, (error, results) => {
    if (error) throw error;
    // console.log(results);
    res.send({ message: "ok", code: 200, results: results });
  });
});

// 显示评论  每页10个  参数页数：page 诗id
server.get("/com", (req, res) => {
  // console.log(req.query.dynasty);
  let n = req.query.page * 1;
  let id = req.query.id;
  let sql = `SELECT comment,user_name FROM user_comment where poem_id='${
    req.query.id
  }' limit ${(n - 1) * 10}, 10;`;
  // 执行SQL语句
  console.log(sql);
  let sql2 = `SELECT COUNT(poem_id) AS count FROM user_comment WHERE poem_id='${id}';`;
  pool.query(sql2, (error, results) => {
    if (error) throw error;
    let rowcount = results[0].count;
    let pagecount = Math.ceil(rowcount / 10);
    console.log(pagecount);
    pool.query(sql, (error, results) => {
      if (error) throw error;
      // console.log(results);
      res.send({
        message: "ok",
        code: 200,
        results: results,
        pagecount: pagecount,
      });
    });
  });
});

//  添加评论到数据库
// 参数： user_id user_name poem_id poem_name author_name comment create_time
server.get("/comment", (req, res) => {
  let uid = req.query.user_id;
  let uname = req.query.user_name;
  let pid = req.query.poem_id;
  let pname = req.query.poem_name;
  let aname = req.query.author_name;
  let com = req.query.comment;
  let ctime = req.query.create_time;

  let sql = `insert into user_comment(user_id, user_name, poem_id, poem_name, author_name, comment, create_time) values(${uid},'${uname}', ${pid}, '${pname}', '${aname}', '${com}', '${ctime}');`;
  // 执行SQL语句
  console.log(sql);
  pool.query(sql, (error, results) => {
    if (error) throw error;
    console.log(results.affectedRows);
    if (results.affectedRows == 1) {
      res.send({ message: "ok", code: 200, results: results });
    } else {
      res.send({ msg: "评论失败" });
    }
  });
});
// 点赞 数据库更新  参数诗ID  n点赞1 取消-1 用户user_id
server.get("/kes", (req, res) => {
  // console.log(req.query.dynasty);
  let n = req.query.n * 1;
  let id = req.query.id;
  let sql1 = `select lke from poems where id='${id}';`;
  // 执行SQL语句
  pool.query(sql1, (error, results) => {
    if (error) throw error;
    console.log(results[0].lke);
    let lke = results[0].lke * 1;

    lke = lke + n;
    let sql2 = `update poems set  lke = '${lke}' where  id ='${id}';`;
    pool.query(sql2, (error, results) => {
      if (error) throw error;
      if (results.affectedRows == 1) {
        res.send({ message: "ok", code: 200, results: results });
      } else {
        res.send({ message: "err", code: 400, results: results });
      }
    });
    let user_id = req.query.user_id;
    let sql5 = `select poem_name from poems where id='${id}';`;
    pool.query(sql5, (error, results) => {
      if (error) throw error;
      let poem_name = results[0].poem_name;
      if (n == 1) {
        let sql3 = `insert into user_likes values(null,${user_id},${id},'${poem_name}',${n},null);`;
        pool.query(sql3, (error, results) => {
          if (error) throw error;
          console.log(results);
        });
      } else {
        let sql4 = `update user_likes set  lke_state = '-1' where  poem_id ='${id}' and user_id = '${user_id}'`;
        pool.query(sql4, (error, results) => {
          if (error) throw error;
          console.log(results);
        });
      }
    });
  });
});

//收藏 参数：诗的ID  s：收藏1 取消-1  用户user_id  数据库更新 并且添加到用户收藏表里
server.get("/sve", (req, res) => {
  let s = req.query.s * 1;
  let id = req.query.id;
  let sql1 = `select sve from poems where id='${id}';`;
  // 执行SQL语句
  pool.query(sql1, (error, results) => {
    if (error) throw error;
    let sve = results[0].sve * 1;
    console.log(sve);
    sve = sve + s;
    console.log(sve);

    let sql2 = `update poems set  sve = '${sve}' where  id ='${id}';`;
    pool.query(sql2, (error, results) => {
      if (error) throw error;
      if (results.affectedRows == 1) {
        res.send({ message: "ok", code: 200, results: results });
      } else {
        res.send({ message: "err", code: 400, results: results });
      }
    });
    let user_id = req.query.user_id;
    let sql5 = `select poem_name from poems where id='${id}';`;
    pool.query(sql5, (error, results) => {
      if (error) throw error;
      console.log(results);
      let poem_name = results[0].poem_name;
      console.log(poem_name);
      if (s == 1) {
        let sql3 = `insert into user_likes values(null,${user_id},${id},'${poem_name}',null,${s});`;
        pool.query(sql3, (error, results) => {
          if (error) throw error;
          console.log(results);
        });
      } else {
        let sql4 = `update user_likes set  sve_state = '-1' where  poem_id ='${id}' and user_id = '${user_id}'`;
        pool.query(sql4, (error, results) => {
          if (error) throw error;
          console.log(results);
        });
      }
    });
  });
});












// 获取特定文章数据的接口
// server.get('/detail', (req, res) => {
//   //获取地址栏中的id参数
//   let id = req.query.id;

//   // SQL查询
//   let sql = "SELECT r.id,subject,content,created_at,nickname,avatar,article_number FROM xzqa_article AS r INNER JOIN xzqa_author AS u ON author_id = u.id WHERE r.id=?";

//   // 执行SQL查询
//   pool.query(sql, [id], (error, results) => {
//     if (error) throw error;
//     // 返回数据到客户端
//     res.send({ message: 'ok', code: 200, result: results[0] });
//   });

// })
